<?php
App::uses('PHPExcel', 'Vendor'.DS.'PHPExcel');
class ReportMenuController extends Controller {
    public $viewClass = 'Smarty';
    public $uses = array('ShipperModel', 'AreaModel', 
    					  'ShippingFeeModel', 'ExportMonthLyDataModel');
	public $objPHPExcel;
	
	public $fileExcelName;

    public function index() {
    	$shipper = $this->ShipperModel->find('all', array(
    												'fields' => 'ShipperModel.shipper_cd, ShipperModel.shipper_name',
    												 'order' => 'id'));
		$firstId = $shipper[0]['ShipperModel']['shipper_cd'];
		// first day
		$firstDay = date("Y-m-01", strtotime("-1 month"));
		// last day
		$lastDay = date("Y-m-t", strtotime("-1 month"));
		$this->set('firstDay', $firstDay);
		$this->set('lastDay', $lastDay);
		$this->set('shipper', $shipper);
		$this->set('firstId', $firstId);
    }
	// shipper start >>> 
    public function frmShipper() {
    	$mode = $this->request->query('mode');
        // get data
        $arrShippers = $this->ShipperModel->getInfoFrmShipper($mode);
		$this->set('mode', $mode);
        $this->set('arrShippers', $arrShippers);
        $this->render('/ReportMenu/frmShipper');
    }

    public function frmShipperPdfHtml() {
        // get data
        $mode = $this->request->query('mode');
        $arrShippers = $this->ShipperModel->getInfoFrmShipper($mode);
        $this->set('arrShippers', $arrShippers);
		$this->set('mode', $mode);
        $this->render('/ReportMenu/frmShipperPdfHtml');
    }
	
    public function frmShipperPdf() {
    	try {
	    	$mode = $this->request->query('mode');
	    	// Set temp file name
			$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
			// Set HTML page
			$htmlPage = BASE_URL . '/frmShipperPdfHtml?mode=' . $mode;
			// Set pdf option
			// $pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1  --footer-html ' . BASE_URL . '/frmFooter';
			$pdfOption = PDF_MARGIN_OPTION;
			//echo '"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"';
			//exit();
			// Excute
			$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
			if (file_exists($tmpFile)) {
				$this->response->file($tmpFile);
			    $this->response->header('Content-Disposition', 'inline');
				// $this->response->header('filename', 'shipper.pdf');
				//$this->response->header('Content-Disposition: inline; filename="shipper.pdf"');
			    return $this->response;
			} else {
				$this->redirect('/frmShipper?mode=' . $mode);
			}
		} catch (Exception $ex) {
			echo $ex->getMessage();
			exit();
			
		}
    }
	// <<< shipper end
	// area start >>> 
    public function frmArea() {
    	$mode = $this->request->query('mode');
        // get data
        $arrArea = $this->AreaModel->getInfoFrmArea($mode);
		$this->set('mode', $mode);
        $this->set('arrArea', $arrArea);
        $this->render('/ReportMenu/frmArea');
    }
    public function frmAreaPdfHtml() {
    	$mode = $this->request->query('mode');
        // get data
        $arrArea = $this->AreaModel->getInfoFrmArea($mode);
		$this->set('mode', $mode);
        $this->set('arrArea', $arrArea);
        $this->render('/ReportMenu/frmAreaPdfHtml');
    }
	
    public function frmAreaPdf() {
    	$mode = $this->request->query('mode');
    	// Set temp file name
		$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
		// Set HTML page
		$htmlPage = BASE_URL . '/frmAreaPdfHtml?mode=' . $mode;
		// Set pdf option
		//$pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
		$pdfOption = PDF_MARGIN_OPTION;
		// Excute
		$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
		if (file_exists($tmpFile)) {
			$this->response->file($tmpFile);
		    $this->response->header('Content-Disposition', 'inline');
		    return $this->response;
		} else {
			$this->redirect('/frmArea?mode=' . $mode);
		}
    }
	// <<< area end
	// Shipping Fee start  >>> 
    public function frmShippingFee() {
    	$mode = $this->request->query('mode');
        // get data
        $arrShipping = $this->ShippingFeeModel->getInfoFrmShippingFee($mode);
		$this->set('mode', $mode);
        $this->set('arrShipping', $arrShipping);
        $this->render('/ReportMenu/frmShippingFee');
    }

    public function frmShippingFeePdfHtml() {
    	$mode = $this->request->query('mode');
        // get data
        $arrShipping = $this->ShippingFeeModel->getInfoFrmShippingFee($mode);
		$this->set('mode', $mode);
        $this->set('arrShipping', $arrShipping);
        $this->render('/ReportMenu/frmShippingFeePdfHtml');
    }
	
    public function frmShippingFeePdf() {
    	$mode = $this->request->query('mode');
    	// Set temp file name
		$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
		// Set HTML page
		$htmlPage = BASE_URL . '/frmShippingFeePdfHtml?mode=' . $mode;
		// Set pdf option
		//$pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
		$pdfOption = PDF_MARGIN_OPTION;
		// Excute
		$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
		if (file_exists($tmpFile)) {
			$this->response->file($tmpFile);
		    $this->response->header('Content-Disposition', 'inline');
		    return $this->response;
		} else {
			$this->redirect('/frmShippingFee?mode=' . $mode);
		}
    }
	// <<< shipping fee end
	// common footer
	public function frmFooter() {
		$this->layout = false;
    	$dateTime = date('Y年m月d日');
		$this->set('dateTime', $dateTime);
        // get data
        $this->render('/ReportMenu/frmFooter');
    }
	
	// ＥＸＣＥＬデータ出力
	public function shippingFeeExcel(){
		$this->objPHPExcel = null;
		$this->fileExcelName = sprintf(str_replace(array('\\\\', '\\'), array('/', '/'), EXCEL_FILE_DIR_TMP), microtime(true) * 10000);
		// export 4 files, download 2 files zip types and save to  disk 2 files
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));
		$arrCond = array(
			'dateFrom' => $dateFrom,
			'dateTo'	=> $dateTo
		);
		// 当月配送手数料一覧表（金額）  -> excel
		$feeListData = $this->ExportMonthLyDataModel->ExportToShippingFeeList($arrCond);
		// メーカー別入出庫保管料一覧２ -> excel
		$kanriListData = $this->ExportMonthLyDataModel->ExportToKanriList($arrCond);
		// save data to Excel
		$this->_exportExcel($feeListData, $kanriListData);
		header("Content-type: application/vnd.ms-excel");    
		header("Content-Disposition: attachment; filename=当月配送手数料一覧表.xls");  
		header("Pragma: no-cache"); 
		readfile($this->fileExcelName);
		// delete files created
		@unlink($this->fileExcelName);
		exit();
	}

	//  荷主別入出庫一覧（選択） by select combobox on the screen
	public function frmDataImageExInputPdf() {
		$SEQ = $this->request->query('SEQ');
    	// Set temp file name
		$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));
		// Set HTML page
		$htmlPage = BASE_URL . '/frmDataImageExInputPdfHtml?SEQ=' . $SEQ . '&dateFrom=' . $dateFrom . '&dateTo=' . $dateTo;
		// Set pdf option
		//$pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
		$pdfOption = PDF_MARGIN_OPTION;
		// Excute
		$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
		if (file_exists($tmpFile)) {
			$this->response->file($tmpFile);
		    $this->response->header('Content-Disposition', 'inline');
		    return $this->response;
		} else {
			$this->redirect('/reports');
		}
    }		
	
	//  荷主別入出庫一覧（選択） by select combobox on the screen
	public function frmDataImageImExInputPdf() {
		$SEQ = $this->request->query('SEQ');
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));		
    	// Set temp file name
		$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
		// Set HTML page
		$htmlPage = BASE_URL . '/frmDataImageImExInputPdfHtml?SEQ=' . $SEQ . '&dateFrom=' . $dateFrom . '&dateTo=' . $dateTo;
		// Set pdf option
		//$pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
		$pdfOption = PDF_MARGIN_OPTION;
		// Excute
		$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
		if (file_exists($tmpFile)) {
			$this->response->file($tmpFile);
		    $this->response->header('Content-Disposition', 'inline');
		    return $this->response;
		} else {
			$this->redirect('/reports');
		}
    }	
			
	// report create data image for using email メール添付用印刷イメージデータ作成
	// CRTSNPDATA2 for export only
	public function frmDataImageExHtml() {
    	$SEQ = $this->request->query('SEQ');
		$dateFrom = $this->request->query('dateFrom');
		$dateTo = $this->request->query('dateTo');			
        $arrData = $this->ExportMonthLyDataModel->shipperShuttetsuListIkkatsu($SEQ, true,$dateFrom, $dateTo );
        $this->set('arrData', $arrData);
        $this->render('/ReportMenu/frmDataImageExHtml');
    }
	// report create data image for using email メール添付用印刷イメージデータ作成
	// CRTSNPDATA for import_export
	public function frmDataImageImExHtml() {
    	$SEQ = $this->request->query('SEQ');
		$dateFrom = $this->request->query('dateFrom');
		$dateTo = $this->request->query('dateTo');		
        $arrData = $this->ExportMonthLyDataModel->shipperNyuShutsuKoIkkatsuSentaku($SEQ, true, $dateFrom, $dateTo);
        $this->set('arrData', $arrData);
        $this->render('/ReportMenu/frmDataImageImExHtml');
    }	
	
	//  荷主別入出庫一覧（選択） by select combobox on the screen
	public function frmDataImageExInputPdfHtml() {
    	$SEQ = $this->request->query('SEQ');
		$dateFrom = $this->request->query('dateFrom');
		$dateTo = $this->request->query('dateTo');			
        $arrData = $this->ExportMonthLyDataModel->shipperShuttetsuListIkkatsu($SEQ, false, $dateFrom, $dateTo );
		
		// delete/insert to temp table
		$this->ExportMonthLyDataModel->insertExportTemp($SEQ, $dateFrom, $dateTo);
        $this->set('arrData', $arrData);
        $this->render('/ReportMenu/frmDataImageExHtml');
    }

	//  荷主別入出庫一覧（選択） by select combobox on the screen
	public function frmDataImageImExInputPdfHtml() {
    	$SEQ = $this->request->query('SEQ');
		$dateFrom = $this->request->query('dateFrom');
		$dateTo = $this->request->query('dateTo');			
        $arrData = $this->ExportMonthLyDataModel->shipperNyuShutsuKoIkkatsuSentaku($SEQ, false, $dateFrom, $dateTo);
        $this->set('arrData', $arrData);
        $this->render('/ReportMenu/frmDataImageImExHtml');
    }	

    public function frmDataImagePdf() {
		 // set timeout + memory
        ini_set("max_execution_time","0");
        ini_set('memory_limit', '-1');    	
		$files = array();
		$folderNamePdf = '/tmp/pdfFolder_';
		$folderZipName = $folderNamePdf .  $this->Session->id . '_' . microtime(true) * 10000;
		// Check the path to store receipt files
        if (!file_exists($folderZipName)) {
            mkdir($folderZipName,0777,true);
        }
    	try {
    		// ｍ_荷主マスタ生成05（レコードカウント）
    		$this->ExportMonthLyDataModel->updateShipperSEQ();
	    	$fileNameCommon = $folderZipName . DS . "%s";
			$arrShipper = $this->ShipperModel->getShipperListForImagePDF();
			if (empty($arrShipper)) {
				// TODO alert no data return
				return 'No have data shipper to print report pdf files';
			}
			$cnt = 1;
			$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
			$dateTo = str_replace("-", "",$this->request->query('dateTo'));			
			foreach($arrShipper as $shipper) {
				$shipper_cd = $shipper['ShipperModel']['shipper_cd'];
				$shipper_nm = $shipper['ShipperModel']['shipper_name'];
				// CRTSNPDATA start >>> 
				// Set HTML page
				// $fileNm = sprintf($fileNameCommon, $cnt . $shipper_nm . '_商品別入出庫料保管料一覧');
				$fileNm = sprintf($fileNameCommon, $cnt . $shipper_cd . '_store_list_' . $this->Session->id . '_' . microtime(true) * 10000);
				// $fileNm = sprintf($fileNameCommon, $cnt . $shipper_nm . '_shipping_import_export_list');
				// delete old file for overwriting
				if (file_exists($fileNm)) {
					@unlink($fileNm);
				}
				$htmlPage = BASE_URL . '/frmDataImageImExHtml?SEQ=' . $shipper_cd . '&dateFrom=' . $dateFrom . '&dateTo=' . $dateTo;
				$this->_exportImagePDF($htmlPage , $fileNm);
				// <<< CRTSNPDATA end
				
				// CRTSNPDATA2 start >>> 
				// Set HTML page
				// $fileNm = sprintf($fileNameCommon, $cnt . $shipper_nm . '_届先配送手数料一覧');
				$fileNm = sprintf($fileNameCommon, $cnt . $shipper_cd . '_fee_list_'. $this->Session->id . '_' . microtime(true) * 10000);
				// delete old file for overwriting
				if (file_exists($fileNm)) {
					@unlink($fileNm);
				}
				$htmlPage = BASE_URL . '/frmDataImageExHtml?SEQ=' . $shipper_cd . '&dateFrom=' . $dateFrom . '&dateTo=' . $dateTo;
				$this->_exportImagePDF($htmlPage , $fileNm);
				$cnt++;
				 // test
				/*
				if ($cnt == 5) {
									break;
								}*/
				
				// <<< CRTSNPDATA2 end				
		}
		$zipFileNameExport = $folderZipName . '.zip';
		//echo sprintf("%s -r %s %s/*", ZIP_EXECUTION_FILE, $zipFileNameExport, $folderZipName);
		//exit();
		exec(sprintf("%s -r %s %s/*", ZIP_EXECUTION_FILE, $zipFileNameExport, $folderZipName), $OUT, $RET);
		//exec("/usr/bin/zip -r /var/www/html/app/webroot/js.zip /var/www/html/app/webroot/js/*");
		//$this->response->file('/var/www/html/app/webroot/js.zip');
		
		header("Content-type: application/zip, application/octet-stream");    
		header("Content-Disposition: attachment; filename=pdfImageExport.zip");  
		header("Pragma: no-cache"); 
		readfile($zipFileNameExport);
		// delete files created
		@unlink($zipFileNameExport);
		exec('rm -rf ' . $folderZipName);
		exit();
		} catch (Exception $ex) {
			echo $ex->getMessage();
		}
    }

	// 商品別入出庫料・保管料一覧　プレビュー
	public function frmShippingStoreFeePdfHtml() {
		 // set timeout + memory
        ini_set("max_execution_time","0");
        ini_set('memory_limit', '-1');
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));
		$sql = $this->ExportMonthLyDataModel->d_02NyuKoEdit1();
		// $mainSQL = 'select sql2.* from ' . $sql . 'as sql2 order by sql2.maker_cd asc limit 1000';
		$mainSQL = "select sql2.* from " . $sql . "as sql2
					where  {$dateFrom}  <= sql2.im_ex_date and sql2.im_ex_date <= {$dateTo}
					 order by sql2.maker_cd asc";
        $arrData = $this->ExportMonthLyDataModel->query($mainSQL);
        $this->set('arrData', $arrData);
        $this->render('/ReportMenu/frmShippingStoreFeePdfHtml');
    }
	
    public function frmShippingStoreFeePdf() {
		 // set timeout + memory
        ini_set("max_execution_time","0");
        ini_set('memory_limit', '-1');  
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));		  	
    	// Set temp file name
		$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
		// Set HTML page
		$htmlPage = BASE_URL . '/frmShippingStoreFeePdfHtml?dateFrom=' . $dateFrom . '&dateTo=' . $dateTo;
		// Set pdf option
		//$pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
		$pdfOption = PDF_MARGIN_OPTION;
		// Excute
		$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
		if (file_exists($tmpFile)) {
			$this->response->file($tmpFile);
		    $this->response->header('Content-Disposition', 'inline');
		    return $this->response;
		} else {
			$this->redirect('/');
		}
    }	

	// 荷主別届先配送手数料　プレビュー
	public function frmShippingFeeListPdfHtml() {
		 // set timeout + memory
        ini_set("max_execution_time","0");
        ini_set('memory_limit', '-1');
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));			
		$arrData = $this->ExportMonthLyDataModel->runD3ShukkoDataEditSQL($dateFrom, $dateTo);
        $this->set('arrData', $arrData);
        $this->render('/ReportMenu/frmShippingFeeListPdfHtml');
    }
	
    public function frmShippingFeeListPdf() {
		 // set timeout + memory
        ini_set("max_execution_time","0");
        ini_set('memory_limit', '-1');    	
		$dateFrom = str_replace("-", "",$this->request->query('dateFrom'));
		$dateTo = str_replace("-", "",$this->request->query('dateTo'));	
    	// Set temp file name
		$tmpFile = sprintf(PDF_RECEIPT_FILE_DIR, microtime(true)*10000 . rand(1,100));
		// Set HTML page
		$htmlPage = BASE_URL . '/frmShippingFeeListPdfHtml?dateFrom=' . $dateFrom . '&dateTo=' . $dateTo;
		// Set pdf option
		//$pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
		$pdfOption = PDF_MARGIN_OPTION;
		// Excute
		$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$tmpFile.'"', $output, $return_var);
		if (file_exists($tmpFile)) {
			$this->response->file($tmpFile);
		    $this->response->header('Content-Disposition', 'inline');
		    return $this->response;
		} else {
			$this->redirect('/');
		}
    }	
	
	private function _exportImagePDF($htmlPage, $file) {
			// Set pdf option
			// $pdfOption = '-s A4 --margin-right 0 --margin-left 0 --zoom 1 --footer-html ' . BASE_URL . '/frmFooter';
			$pdfOption = PDF_MARGIN_OPTION;
			// Excute
			$result = exec('"'.HTML_TO_PDF_EXE_PATH . '" ' . $pdfOption . ' ' . '"' . $htmlPage . '"' . ' ' . '"'.$file.'"', $output, $return_var);
	}
	private function _exportExcel($dataFee, $dataKanri) {
		try {
				$this->objPHPExcel = new PHPExcel();
				// fee >>> 
				$this->objPHPExcel->setActiveSheetIndex(0);
				$this->objPHPExcel->getActiveSheet()->setTitle('当月配送手数料一覧表（金額）');
				// add value for worksheet
				$row = 1;
	            foreach ($dataFee as $item) {
	            	$colum = 'A';
	            	foreach ($item[0] as $value) {
	            		if ($row == 1) {
	            			$this->_cellColor($colum . $row, 'C0C0C0');
	            		}
	            		$this->objPHPExcel->getActiveSheet()->setCellValue($colum . $row, strip_tags($value));
						$colum++;
	            	}
					$row++;
	            };		
				// <<< fee		
				$newSheet = new PHPExcel_Worksheet($this->objPHPExcel, 'メーカー別入出庫保管料一覧２');
				$this->objPHPExcel->addSheet($newSheet, 1);
				$this->objPHPExcel->setActiveSheetIndex(1);
				$row = 1;
	            foreach ($dataKanri as $item) {
	            	$colum = 'A';
	            	foreach ($item[0] as $value) {
	            		if ($row == 1) {
	            			$this->_cellColor($colum . $row, 'C0C0C0');
	            		}
	            		$this->objPHPExcel->getActiveSheet()->setCellValue($colum . $row, strip_tags($value));
						$colum++;
	            	}
					$row++;
	            };						
	        // $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
	        $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
			$objWriter->save($this->fileExcelName); 			
		} catch (Exception $ex) {
			echo $ex->getMessage();
			return '';
		}
	}
    function _cellColor($cells,$color){
        $this->objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()
		        ->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
		        'startcolor' => array('rgb' => $color)
        ));
    }
}
